import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author jesus
 */
public class ConexionMySQL {
    static Connection conn;

    public ConexionMySQL(Connection conn){
        conn=null;
    }

    public static void Conectar(String bd,String log,String pass,String url){
        try{
            Class.forName("com.mysql.jdbc.Driver");
            conn=(Connection)DriverManager.getConnection(url, log, pass);
        }catch(SQLException ex){
           System.out.println("Hubo un problema al intentar conectarse con la base de datos "+url+"\n");
           System.out.println(ex);
        }
        catch(ClassNotFoundException ex){
            System.out.println(ex);
        }
    }
    
    public static int NumerosDocs(){
    	Statement stmp;
    	ResultSet rs = null;
    	int docs = 0;
    	
    	try {
			stmp = conn.createStatement();
			rs = stmp.executeQuery("Select count(*) as docs From doc"); 
			rs.next();
			docs = rs.getInt(1);
			
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return docs;
    }
    
    //Devuelve un objeto Documento por el documento con id en la BD
    public static ArrayList<String> SeleccionarPalabrasDoc(int id){
    	Statement stmp;
    	ResultSet rs = null;
    	Documento d = null;
    	ArrayList<String> words = new ArrayList<String>();
		try {
			stmp = conn.createStatement();
//			 int aux=stmp.executeUpdate("INSERT INTO asignatura VALUES("+id+",\""+nom+"\","+curso+",\""+NProf+"\",\""+ApProf+"\")");
			rs = stmp.executeQuery("SELECT * FROM com WHERE doc="+id); 

			while (rs.next()){
				words.add(rs.getString("pal"));
	    	}
//			d=new Documento(id,words);
			rs.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
    	return words;
    	
    }

    public static void InsertarTuplaAsignatura(int id,String nom,int curso,String NProf,String ApProf) throws Exception{
        try{
            Statement stmp=conn.createStatement();
            int aux=stmp.executeUpdate("INSERT INTO asignatura VALUES("+id+",\""+nom+"\","+curso+",\""+NProf+"\",\""+ApProf+"\")");
            stmp.close();
        }catch (SQLException e){
            System.out.println("Error de MySQL:(insertar asignatura) "+ e.getMessage());
        }
        catch (Exception e){
            System.out.println("Error inesperado:(insertar asignatura) " +e.getMessage());
        }

    }

    public static void InsertarTuplaAlumno(int id,String nom,String Ap,String NTutor,String ApTutor) throws Exception{
        try{
            Statement stmp=conn.createStatement();
            int aux=stmp.executeUpdate("INSERT INTO alumno VALUES("+id+",\""+nom+"\",\""+Ap+"\",\""+NTutor+"\",\""+ApTutor+"\")");
            stmp.close();
        }catch (SQLException e){
            System.out.println("Error de MySQL(insertar alumno): "+ e.getMessage());
        }
        catch (Exception e){
            System.out.println("Error inesperado(insertar alumno): " +e.getMessage());
        }
    }

    public static void ModificarTuplaAsignatura(int id, String columna,String valor){
         
        try{
            Statement stmp=conn.createStatement();
            int aux=stmp.executeUpdate("UPDATE asignatura SET "+columna+"=\""+valor+"\" WHERE idAsig="+id);
            stmp.close();
        }catch (SQLException e){
            System.out.println("Error de MySQL(ModAsigCadena): "+ e.getMessage());
        }
        catch (Exception e){
            System.out.println("Error inesperado(ModAsigCadena): " +e.getMessage());
        }
    }

    public static void ModificarTuplaAsignatura(int id, String columna,int valor){
    
        try{
            Statement stmp=conn.createStatement();
            int aux=stmp.executeUpdate("UPDATE asignatura SET "+columna+"="+valor+" WHERE idAsig="+id);
            stmp.close();
        }catch (SQLException e){
            System.out.println("Error de MySQL(ModAsigEntero): "+ e.getMessage());
        }
        catch (Exception e){
            System.out.println("Error inesperado(ModAsigEntero): " +e.getMessage());
        }
    }

    public static void VistaPreliminarAsignaturas(){
        try{
            Statement stmp=conn.createStatement();
            ResultSet r=stmp.executeQuery("SELECT * FROM asignatura");
            System.out.format("\n%15s %15s %15s %15s %15s\n","idAsig","nombreAsig","curso","nombProf","apellidoProf");
            System.out.println("    ----------------------------------------------------------------------------------");
            while(r.next()){
                int i=r.getInt("idAsig");
                String s=r.getString("nombreAisg");
                int i2=r.getInt("curso");
                String s2=r.getString("nombProf");
                String s3=r.getString("apellidoProf");
                System.out.format("%15d %15s %15d %15s %15s\n", i,s,i2,s2,s3);
         
            }
            System.out.println();
            stmp.close();
        }catch (SQLException e){
            System.out.println("Error de MySQL(Vista Preliminar): "+ e.getMessage());
        }
        catch (Exception e){
            System.out.println("Error inesperado(Vista Preliminar): " +e.getMessage());
        }
    }


    public static void ConsultarAsignaturasPorAlumno(String apellido) throws Exception{
        try{
            Statement stmp=conn.createStatement();

            String consulta="SELECT nombre, apellido,nombreAisg ";
            consulta=consulta+"FROM alumno,asignatura,matricular ";
            consulta=consulta+"WHERE matricular.alu=alumno.idalumno AND ";
            consulta=consulta+"matricular.asig=idAsig AND apellido=\""+apellido+"\"";

            ResultSet r=stmp.executeQuery(consulta);
            System.out.format("\n%15s %15s %15s\n","Nombre","Apellido","nombreAsig");
            System.out.println("    -------------------------------------------------------");
            while(r.next()){
                String s1=r.getString("nombre");
                String s2=r.getString("apellido");
                String s3=r.getString("nombreAisg");
                System.out.format("%15s %15s %15s\n",s1,s2,s3);

            }
            System.out.println();
            stmp.close();
        }catch (SQLException e){
            System.out.println("Error de MySQL:(consulta) "+ e.getMessage());
        }
        catch (Exception e){
            System.out.println("Error inesperado:(consulta) " +e.getMessage());
        }

    }

    public static void Desconectar()throws Exception{
        conn.close();

    }
}